CREATE TABLE "user"
(
  id serial NOT NULL,
  fio character varying NOT NULL,
  birthday character varying,
  "login" character varying NOT NULL,
  "password" character varying NOT NULL,
  status boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_user PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE "user" OWNER TO postgres;

CREATE TABLE image
(
  id serial NOT NULL,
  id_user integer NOT NULL,
  description character varying,
  date_add character varying,
  image_width integer,
  image_height integer,
  "name" character varying,
  path character varying,
  CONSTRAINT pk_image PRIMARY KEY (id),
  CONSTRAINT fk_image_user FOREIGN KEY (id_user)
      REFERENCES "user" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE image OWNER TO postgres;

CREATE OR REPLACE FUNCTION set_status_user()
  RETURNS trigger AS
$BODY$begin
update "user" set "status"=FALSE
where "id"=new."id";
return new;
end$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION set_status_user() OWNER TO postgres;

CREATE OR REPLACE FUNCTION set_date_add()
  RETURNS trigger AS
$BODY$begin
update "image" set "date_add"=CURRENT_DATE
where "id"=new."id";
return new;
end$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION set_date_add() OWNER TO postgres;

CREATE TRIGGER set_status
  AFTER INSERT
  ON "user"
  FOR EACH ROW
  EXECUTE PROCEDURE set_status_user();

CREATE TRIGGER set_date_add
  AFTER INSERT
  ON image
  FOR EACH ROW
  EXECUTE PROCEDURE set_date_add();
